import requests
import sqlite3
import pandas as pd
import time
import datetime
from bs4 import BeautifulSoup
from estocks.comm.tsapis import ts,logger,Tsapi
TimeOut = 5
tb_nm = 'top10_holder'

def tableCheck(conn):
    curs = conn.cursor()
    sql = "SELECT name num FROM sqlite_master WHERE type='table' AND name='{0}'".format(tb_nm)
    curs.execute(sql)
    isexists = curs.fetchall()
    if len(isexists) > 0:
        curs.close()
    else:
        sql = """CREATE TABLE top10_holder (
         stock_cd TEXT,
         rpt_dt TEXT,
         holder_nm TEXT,
         hold_vol REAL,
         hold_rate REAL,
         chng_typ TEXT,
         chag_vol REAL
         )"""
        curs.execute(sql)
        curs.close()


def get_last_rpt_dt(conn):
    curs = conn.cursor()
    sql = "SELECT stock_cd, max(rpt_dt) rpt_dt FROM {0} group by stock_cd".format(tb_nm)
    curs.execute(sql)
    dates = curs.fetchall()
    if len(dates) > 0:
        return dict(dates)
    else:
        return {'1000': '2020-01-01'}


def to_float(str_num):
    try:
        return float(str_num)
    except Exception:
        return 0;


def gpdmgd_top10(stock_code):
    url = "http://cwzx.shdjt.com/gpdmgd.asp?gpdm=" + str(stock_code)
    logger.info(url)
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36'};
    result = requests.session().get(url, headers=headers, timeout=TimeOut)  # requests.get(url);
    soup = BeautifulSoup(result.text, "html.parser")
    tbs1 = soup.find_all("table")
    arr = []
    for tb1 in tbs1:
        tbs2 = tb1.find_all("table")
        for tb2 in tbs2:
            tbs3 = tb2.find_all("table")
            for tb3 in tbs3:
                trs = tb3.find_all('tr')
                for tr in trs:
                    tds = tr.find_all('td')
                    if len(tds) > 14 and ('十大股东' in tds[6].text or '股东类型' in tds[6].text):
                        tp = []
                        for td in tds:
                            tp.append(td.text)
                        arr.append(tp)
    if len(arr) > 1:
        df = pd.DataFrame(arr[1:], columns=arr[0])
        renames = {'股票代码': 'stock_cd', '更新日期': 'rpt_dt', '股东名称': 'holder_nm', '持股数(万)': 'hold_vol',
                   '比例': 'hold_rate', '增减仓': 'chng_typ', '数量': 'chag_vol'}
        df.rename(columns=renames, inplace=True)
        df['hold_vol'] = df['hold_vol'].astype(float)
        df['hold_rate'] = df['hold_rate'].astype(float)
        df['chag_vol'] = df['chag_vol'].apply(to_float)
        df = df[list(renames.values())]
        df['rpt_dt'] = pd.to_datetime(df['rpt_dt'])
        df['rpt_dt'] = df['rpt_dt'].apply(lambda x: str(x)[0:10])
        # df.drop(columns='序',inplace=True,errors='ignore')
        return df
    else:
        return pd.DataFrame()


if __name__ == '__main__':
    ts=Tsapi()
    if 'stocks' not in dir():
        stocks=ts.get_stock_list()
        # 查询当前所有正常上市交易的股票列表
    tableCheck(ts.conn.get_conn_refresh())
    stock_max_rpt_dt = get_last_rpt_dt(ts.conn.get_conn_refresh())
    stlist = list(stocks['stock_cd'])
    total = len(stocks)
    today = datetime.datetime.today()
    last_q_end = (today + pd.tseries.offsets.DateOffset(months=-((today.month - 1) % 3),
                                                        days=1 - today.day - 1)).strftime('%Y-%m-%d')  # 当季第一天
    i = 0
    for code in stlist:
        i = i + 1
        max_rpt_dt = stock_max_rpt_dt.get(code, '2020-01-01')
        logger.info("正在获取第", i, "个,共：", total, "-----", code, max_rpt_dt, last_q_end)
        if max_rpt_dt < last_q_end:
            df = gpdmgd_top10(code)
            df = df[df['rpt_dt'] > max_rpt_dt]
            df.to_sql(tb_nm, ts.conn.get_conn_refresh(), if_exists='append', index=False)
            time.sleep(3)  # sleep